﻿using Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class PrescriptionDA: DBConnection
    {
        public PrescriptionDA()
        {
            sqlCommand.Connection = sqlConnection;
        }

        public int addPrescription(Prescription pre)
        {
            string query = "insert into " + Common.TOA_MAU +
                " values(@prescriptionname,@note); select SCOPE_IDENTITY();";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@prescriptionname", pre.getPrescriptionName());
            sqlCommand.Parameters.AddWithValue("@note", pre.getPrescriptionNote());
            try
            {
                open();
                int idRe = -1;
                sqlCommand.CommandText = query;
                string re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return -1;
            }
        }

        public bool updatePrescription(Prescription pre)
        {
            //log here
            int preId = pre.getPrescriptionId();
            string query = "update " + Common.TOA_MAU +
                " set prescriptionname = @prescriptionname, " +
                "note = @note " +
                "where prescriptionid = " + preId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@prescriptionname", pre.getPrescriptionName());
            sqlCommand.Parameters.AddWithValue("@note", pre.getPrescriptionNote());
            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }

        public Prescription getPrescriptionById(int preId)
        {
            string query = "select * from " + Common.TOA_MAU + " where prescriptionid=" + preId;
            sqlCommand.CommandText = query;
            try
            {

                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                Prescription pre = new Prescription();
                while (sqlDataReader.Read())
                {
                    pre = createPrescriptionFromReader(sqlDataReader);

                }
                close();
                return pre;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        public Prescription[] getAllPrescription()
        {
            string query = "select * from " + Common.TOA_MAU;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Prescription> liPre = new List<Prescription>();
                Prescription pre = new Prescription();
                while (sqlDataReader.Read())
                {
                    pre = createPrescriptionFromReader(sqlDataReader);
                    liPre.Add(pre);
                }
                close();
                return liPre.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        public Prescription[] getPrescriptionsByName(string name)
        {
            string query = "select * from " + Common.TOA_MAU +
                " where prescriptionname = N'" + name + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Prescription> liPre = new List<Prescription>();
                Prescription pre = new Prescription();
                while (sqlDataReader.Read())
                {
                    pre = createPrescriptionFromReader(sqlDataReader);
                    liPre.Add(pre);
                }
                close();
                return liPre.ToArray();
            }
            catch
            {
                //log here
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }

        }

        /// <summary>
        /// Get an array of patients from a custom query
        /// </summary>
        /// <param name="query">The custom query</param>
        /// <returns>An array of patients</returns>
        public Prescription[] getPrescriptionsByRawQuery(string query)
        {
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Prescription> liPre = new List<Prescription>();
                Prescription pre = new Prescription();
                while (sqlDataReader.Read())
                {
                    string str = "";
                    for (int i = 0; i < sqlDataReader.FieldCount; i++)
                    {
                        str = sqlDataReader.GetName(i);//Get name of column;

                        str.ToLower();
                        switch (str)
                        {
                            case "prescriptionid":
                                pre.setPrescriptionId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "prescriptionname":
                                pre.setPrescriptionName(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "note":
                                pre.setPrescriptionNote(sqlDataReader.GetValue(i).ToString());
                                break;
                        }
                    }
                    liPre.Add(pre);
                }
                close();
                return liPre.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        private Prescription createPrescriptionFromReader(SqlDataReader rd)
        {

            if (rd != null && !rd.IsClosed)
            {
                Prescription pre = new Prescription();
                pre.setPrescriptionId(rd.GetInt32(0));
                pre.setPrescriptionName(rd.GetString(1));
                pre.setPrescriptionNote(rd.GetString(2));
                return pre;
            }
            else
            {
                return null;
            }

        }
        
        /////////////////////////////////////////////////
        //
        // Prescription detail
        //
        /////////////////////////////////////////////////

        public bool addPrescriptionDetail(PrescriptionDetail preDe)
        {
            string query = "insert into " + Common.CHI_TIET_TOA_MAU +
                " values(@prescriptionid,@medicineid, @medicinemorning, @medicinenoon,@medicineafternoon,@medicineevening,"+
                "@quantitytotal,@dosage)";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@prescriptionid", preDe.getPrescriptionId());
            sqlCommand.Parameters.AddWithValue("@medicineid", preDe.getMedicineId());
            sqlCommand.Parameters.AddWithValue("@medicinemorning", preDe.getMorningMed());
            sqlCommand.Parameters.AddWithValue("@medicinenoon", preDe.getNoonMed());
            sqlCommand.Parameters.AddWithValue("@medicineafternoon", preDe.getAfternoonMed());
            sqlCommand.Parameters.AddWithValue("@medicineevening", preDe.getEveningMed());
            sqlCommand.Parameters.AddWithValue("@quantitytotal", preDe.getQuantity());
            sqlCommand.Parameters.AddWithValue("@dosage", preDe.getDosage());
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return false;
            }

        }

        public bool updatePrescriptionDetail(PrescriptionDetail preDe)
        {
            //log here
            int preDeId = preDe.getDetailId();
            string query = "update " + Common.CHI_TIET_TOA_MAU +
                " set prescriptionid = @prescriptionid, " +
                "medicineid = @medicineid " +
                "medicinemorning = @medicinemorning " +
                "medicinenoon = @medicinenoon " +
                "medicineafternoon = @medicineafternoon " +
                "medicineevening = @medicineevening " +
                "quantitytotal = @quantitytotal " +
                "dosage = @dosage " +
                "where prescriptiondetailid = " + preDeId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@prescriptionid", preDe.getPrescriptionId());
            sqlCommand.Parameters.AddWithValue("@medicineid", preDe.getMedicineId());
            sqlCommand.Parameters.AddWithValue("@medicinemorning", preDe.getMorningMed());
            sqlCommand.Parameters.AddWithValue("@medicinenoon", preDe.getNoonMed());
            sqlCommand.Parameters.AddWithValue("@medicineafternoon", preDe.getAfternoonMed());
            sqlCommand.Parameters.AddWithValue("@medicineevening", preDe.getEveningMed());
            sqlCommand.Parameters.AddWithValue("@quantitytotal", preDe.getQuantity());
            sqlCommand.Parameters.AddWithValue("@dosage", preDe.getDosage());
            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }

        public PrescriptionDetail getPrescriptionDetailById(int preDeId)
        {
            string query = "select * from " + Common.TOA_MAU + " where prescriptiondetailid=" + preDeId;
            sqlCommand.CommandText = query;
            try
            {

                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                PrescriptionDetail preDe = new PrescriptionDetail();
                while (sqlDataReader.Read())
                {
                    preDe = createPrescriptionDetailFromReader(sqlDataReader);

                }
                close();
                return preDe;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        public PrescriptionDetail[] getAllPrescriptionDetail()
        {
            string query = "select * from " + Common.CHI_TIET_TOA_MAU;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<PrescriptionDetail> liPreDe = new List<PrescriptionDetail>();
                PrescriptionDetail preDe = new PrescriptionDetail();
                while (sqlDataReader.Read())
                {
                    preDe = createPrescriptionDetailFromReader(sqlDataReader);
                    liPreDe.Add(preDe);
                }
                close();
                return liPreDe.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        
        /// <summary>
        /// Get an array of patients from a custom query
        /// </summary>
        /// <param name="query">The custom query</param>
        /// <returns>An array of patients</returns>
        public PrescriptionDetail[] getPrescriptionDetailsByRawQuery(string query)
        {
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<PrescriptionDetail> liPreDe = new List<PrescriptionDetail>();
                PrescriptionDetail preDe = null;
                while (sqlDataReader.Read())
                {
                    string str = "";
                    preDe = new PrescriptionDetail();
                    for (int i = 0; i < sqlDataReader.FieldCount; i++)
                    {
                        str = sqlDataReader.GetName(i);//Get name of column;

                        str.ToLower();
                        switch (str)
                        {
                            case "prescriptiondetailid":
                                preDe.setDetailId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "prescriptionid":
                                preDe.setPrescriptionId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicineid":
                                preDe.setMedicineId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicinemorning":
                                preDe.setMorningMed(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicinenoon":
                                preDe.setNoonMed(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicineafternoon":
                                preDe.setAfternoonMed(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "medicineevening":
                                preDe.setEveningMed(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "quantitytotal":
                                preDe.setQuantity(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "dosage":
                                preDe.setDosage(sqlDataReader.GetValue(i).ToString());
                                break;
                        }
                    }
                    liPreDe.Add(preDe);
                }
                close();
                return liPreDe.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        private PrescriptionDetail createPrescriptionDetailFromReader(SqlDataReader rd)
        {

            if (rd != null && !rd.IsClosed)
            {
                PrescriptionDetail preDe = new PrescriptionDetail();
                preDe.setDetailId(rd.GetInt32(0));
                preDe.setPrescriptionId(rd.GetInt32(1));
                preDe.setMedicineId(rd.GetInt32(2));
                preDe.setMorningMed(rd.GetInt32(3));
                preDe.setNoonMed(rd.GetInt32(4));
                preDe.setAfternoonMed(rd.GetInt32(5));
                preDe.setEveningMed(rd.GetInt32(6));
                preDe.setQuantity(rd.GetInt32(7));
                preDe.setDosage(rd.GetString(8));
                return preDe;
            }
            else
            {
                return null;
            }

        }
        
    }
}
